package Reports;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Query {
	static Connection conn = null;

	public Query() {
		initSql();
	}

	public static void initSql() {
		final String driverClass = "oracle.jdbc.driver.OracleDriver";
//		final String connectionString = "jdbc:oracle:thin:@10.1.7.9:1521:tc";
//		final String userName = "dataexchange";
//		final String passwd = "dataexchange123";
		final String connectionString = "jdbc:oracle:thin:@10.1.7.102:1521:tctest";
		final String userName = "gou";
		final String passwd = "gou123";

		if (conn == null)
			try {
				conn = DBConnecter.getConnection(driverClass, connectionString,
						userName, passwd);
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public Map<String, List> QueryInfo() {
		String sql = "SELECT TS.SUPPLIERNAME,TS.SUPPLIERCODE,TC.SCNAME,TC.SCEMAIL from  CHYIII_DEC_TSUPPLIER ts,CHYIII_DEC_TSUPPLIERCONTACT tc where ts.SUPPLIERCODE = TC.SUPPLIERCODE and TC.SCEMAIL in(SELECT SCEMAIL FROM   CHYIII_DEC_TSUPPLIERCONTACT group by SCEMAIL having count(*)>1) ORDER BY  TC.SCEMAIL ";
		PreparedStatement pstat = null;
		Map<String, List> map = new LinkedHashMap<String, List>();
		List<String[]> arrylist = new ArrayList<String[]>();
		String tempmail = "";
		try {
			pstat = conn.prepareStatement(sql);
			ResultSet rs = pstat.executeQuery();
			while (rs.next()) {
				String company = rs.getString(1);
				String code = rs.getString(2);
				String name = rs.getString(3);
				String mail = rs.getString(4);
				if (tempmail.compareTo(mail) != 0) {
					arrylist = new ArrayList<String[]>();
				}
				tempmail = mail;
				String[] arr = { company, code, name };
				arrylist.add(arr);
				map.put(mail, arrylist);
			}

			String sql2 = "SELECT TD.DEPARTMENTNAME,TD.DEPARMENTTCODE,TC.SCNAME,TC.SCEMAIL from  CHYIII_DEC_TDEPARTMENT TD,CHYIII_DEC_TSUPPLIERCONTACT TC where TD.DEPARMENTTCODE = TC.DEPTCODE and TC.SCEMAIL in(SELECT SCEMAIL FROM   CHYIII_DEC_TSUPPLIERCONTACT group by SCEMAIL having count(*)>1) ORDER BY  TC.SCEMAIL ";
			pstat = conn.prepareStatement(sql2);
			rs = pstat.executeQuery();
			arrylist = new ArrayList<String[]>();
			while (rs.next()) {
				String company = rs.getString(1);
				String code = rs.getString(2);
				String name = rs.getString(3);
				String mail = rs.getString(4);
				if (tempmail.compareTo(mail) != 0) {
					arrylist = new ArrayList<String[]>();
				}
				tempmail = mail;
				String[] arr = { company, code, name };
				arrylist.add(arr);
				map.put(mail, arrylist);
			}
			QuerySupplierDepart q = new QuerySupplierDepart(conn);
			map = q.Query(map);
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			try {
				if (pstat != null)
					pstat.close();
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}

}
